package net.board.db;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import javax.naming.Context;
import javax.naming.InitialContext;
import javax.sql.DataSource;

import Connect.connector;

public class BoardDAO {
	Connection con;
	PreparedStatement pstmt;
	ResultSet rs;
	//���� ���� ���ϱ�.
	public int getListCount() {
		int x= 0;
		
		try{
			
			con=connector.getConnection();
			System.out.println("getConnection");
			pstmt=con.prepareStatement("select count(*) from qna");
			rs = pstmt.executeQuery();
			
			if(rs.next()){
				x=rs.getInt(1);
			}
		}catch(Exception ex){
			
		}finally{
			connector.releaseConnection(con, pstmt, rs);
		}
		return x;
	}
	
	public int getListCount(String search, String searchworld) {
		int x= 0;
		
		try{
			
			con=connector.getConnection();
			System.out.println("getConnection");
			pstmt=con.prepareStatement("select count(*) from qna where " + search +" like '%"+searchworld+"%'");
			rs = pstmt.executeQuery();
			
System.out.println(pstmt);		

			if(rs.next()){
				x=rs.getInt(1);
			}
		}catch(Exception ex){
			
		}finally{
			connector.releaseConnection(con, pstmt, rs);
		}
		return x;
	}
	
	public List getBoardList(int page,int limit){
	
		
		
		List<BoardBean> list = new ArrayList<BoardBean>();
	
		
		int startrow=(page-1)*10; 
		int endrow=limit; 
		System.out.println("firstpage : " + startrow);
		System.out.println("endpage : " + endrow);
		String board_list_sql="select * from qna order by QNA_REF desc, QNA_SEQ asc limit ?,?" ;
		try{
			con = connector.getConnection();
			pstmt = con.prepareStatement(board_list_sql);
			pstmt.setInt(1, startrow);
			pstmt.setInt(2, endrow);
			rs = pstmt.executeQuery();
			while(rs.next()){
			
				BoardBean board = new BoardBean();
				board.setID(rs.getInt("ID"));
				board.setMember_Id(rs.getInt("Member_Id"));
				board.setQNA_NICK(rs.getString("QNA_NICK"));
				board.setQNA_PASS(rs.getString("QNA_PASS"));
				board.setQNA_SUBJECT(rs.getString("QNA_SUBJECT"));
				board.setQNA_CONTENT(rs.getString("QNA_CONTENT"));
				board.setQNA_FILE(rs.getString("QNA_FILE"));
				board.setQNA_REF(rs.getInt("QNA_REF"));
				board.setQNA_LEV(rs.getInt("QNA_LEV"));
				board.setQNA_SEQ(rs.getInt("QNA_SEQ"));
				board.setQNA_READCOUNT(rs.getInt("QNA_READCOUNT"));
				board.setQNA_DATE(rs.getDate("QNA_DATE"));
				list.add(board);
			}
			
			return list;
		}catch(Exception ex){
			System.out.println("getBoardList ���� : " + ex);
		}finally{
			connector.releaseConnection(con, pstmt, rs);
		}
		return null;
	}
	
	public List getBoardList(int page,int limit, String search, String searchword){
	
		
		
		List<BoardBean> list = new ArrayList<BoardBean>();
	
		
		int startrow=(page-1)*10; 
		int endrow=limit; 
		System.out.println("firstpage : " + startrow);
		System.out.println("endpage : " + endrow);
		String board_list_sql="select * from (select * from qna where "+search+" like '%"+searchword+"%' ORDER BY QNA_REF DESC, QNA_SEQ asc)a  limit ?, ?" ;
System.out.println(board_list_sql);		
		try{
			con = connector.getConnection();
			pstmt = con.prepareStatement(board_list_sql);
			pstmt.setInt(1, startrow);
			pstmt.setInt(2, endrow);
			rs = pstmt.executeQuery();
			while(rs.next()){
			
				BoardBean board = new BoardBean();
				board.setID(rs.getInt("ID"));
				board.setMember_Id(rs.getInt("Member_Id"));
				board.setQNA_NICK(rs.getString("QNA_NICK"));
				board.setQNA_PASS(rs.getString("QNA_PASS"));
				board.setQNA_SUBJECT(rs.getString("QNA_SUBJECT"));
				board.setQNA_CONTENT(rs.getString("QNA_CONTENT"));
				board.setQNA_FILE(rs.getString("QNA_FILE"));
				board.setQNA_REF(rs.getInt("QNA_REF"));
				board.setQNA_LEV(rs.getInt("QNA_LEV"));
				board.setQNA_SEQ(rs.getInt("QNA_SEQ"));
				board.setQNA_READCOUNT(rs.getInt("QNA_READCOUNT"));
				board.setQNA_DATE(rs.getDate("QNA_DATE"));
				list.add(board);
			}
			
			return list;
		}catch(Exception ex){
			System.out.println("getBoardList ���� : " + ex);
		}finally{
			connector.releaseConnection(con, pstmt, rs);
		}
		return null;
	}
	
	//�� ���� ����.
	public BoardBean getDetail(int ID) throws Exception{
		
		BoardBean board = null;
		try{
			con = connector.getConnection();
			pstmt = con.prepareStatement(
					"select * from qna where ID = ?");
			pstmt.setInt(1, ID);
			
			rs= pstmt.executeQuery();
			
			if(rs.next()){
				board = new BoardBean();
				board.setID(rs.getInt("ID"));
				board.setMember_Id(rs.getInt("Member_Id"));
				board.setQNA_NICK(rs.getString("QNA_NICK"));
				board.setQNA_PASS(rs.getString("QNA_PASS"));
				board.setQNA_SUBJECT(rs.getString("QNA_SUBJECT"));
				board.setQNA_CONTENT(rs.getString("QNA_CONTENT"));
				board.setQNA_FILE(rs.getString("QNA_FILE"));
				board.setQNA_REF(rs.getInt("QNA_REF"));
				board.setQNA_LEV(rs.getInt("QNA_LEV"));
				board.setQNA_SEQ(rs.getInt("QNA_SEQ"));
				board.setQNA_READCOUNT(rs.getInt("QNA_READCOUNT"));
				board.setQNA_DATE(rs.getDate("QNA_DATE"));
			}
			return board;
		}catch(Exception ex){
		}finally{
			connector.releaseConnection(con, pstmt, rs);
		}
		return null;
	}
	
	//�� ���.
	public boolean boardInsert(BoardBean board){
		
		int ID =0;
		String sql="";
		
		int result=0;
		
		try{
			con = connector.getConnection();
			pstmt=con.prepareStatement("select max(ID) from qna");
			rs = pstmt.executeQuery();
			
			if(rs.next())
				ID =rs.getInt(1)+1;
			else
				ID=1;
			
			sql="insert into qna (ID, Member_Id, QNA_NICK, QNA_PASS,";
			sql+="QNA_SUBJECT, QNA_CONTENT, QNA_FILE, QNA_REF,"+
				"QNA_LEV, QNA_SEQ, QNA_READCOUNT,"+
				"QNA_DATE) values(?,?,?,?,?,?,?,?,?,?,?,sysdate())";
			
			pstmt = con.prepareStatement(sql);
			pstmt.setInt(1, ID);
			pstmt.setInt(2, board.getMember_Id());
			pstmt.setString(3, board.getQNA_NICK());
			pstmt.setString(4, board.getQNA_PASS());
			pstmt.setString(5, board.getQNA_SUBJECT());
			pstmt.setString(6, board.getQNA_CONTENT());
			pstmt.setString(7, board.getQNA_FILE());
			pstmt.setInt(8, ID);
			pstmt.setInt(9, 0);
			pstmt.setInt(10, 0);
			pstmt.setInt(11, 0);
			
			result=pstmt.executeUpdate();
			if(result==0)return false;
			
			return true;
		}catch(Exception ex){
			System.out.println("boardInsert ���� : "+ex);
		}finally{
			connector.releaseConnection(con, pstmt, rs);
		}
		return false;
	}
	
	//�� �亯.
	public int boardReply(BoardBean board){
		
		String board_max_sql="select max(ID) from qna";
		String sql="";
		int ID=0;
		int result=0;
		
		int re_ref=board.getQNA_REF();
		int re_lev=board.getQNA_LEV();
		int re_seq=board.getQNA_SEQ();
		
		try{
			con = connector.getConnection();
			pstmt=con.prepareStatement(board_max_sql);
			rs = pstmt.executeQuery();
			if(rs.next())ID =rs.getInt(1)+1;
			else ID=1;
			System.out.println("�۴亯");
			sql="update qna set QNA_SEQ=QNA_SEQ+1 where QNA_REF=? ";
			sql+="and QNA_SEQ>?";
			
			pstmt = con.prepareStatement(sql);
			pstmt.setInt(1,re_ref);
			pstmt.setInt(2,re_seq);
			result=pstmt.executeUpdate();
			
			re_seq = re_seq + 1;
			re_lev = re_lev+1;
			
			sql="insert into qna (ID ,Member_Id, QNA_NICK, QNA_PASS,";
			sql+="QNA_SUBJECT, QNA_CONTENT, QNA_FILE, QNA_REF, QNA_LEV, QNA_SEQ,";
			sql+="QNA_READCOUNT, QNA_DATE) values(?,?,?,?,?,?,?,?,?,?,?,sysdate())";
			
			pstmt = con.prepareStatement(sql);
			pstmt.setInt(1, ID);
			pstmt.setInt(2, board.getMember_Id());
			pstmt.setString(3, board.getQNA_NICK());
			pstmt.setString(4, board.getQNA_PASS());
			pstmt.setString(5, board.getQNA_SUBJECT());
			pstmt.setString(6, board.getQNA_CONTENT());
			pstmt.setString(7, board.getQNA_FILE()); //���忡�� ������ ���ε����� ����.
			pstmt.setInt(8, re_ref);
			pstmt.setInt(9, re_lev);
			pstmt.setInt(10, re_seq);
			pstmt.setInt(11, 0);
			pstmt.executeUpdate();
			return ID;
		}catch(SQLException ex){
			System.out.println("boardReply ���� : "+ex);
		}finally{
			connector.releaseConnection(con, pstmt, rs);
		}
		return 0;
	}
	
	//�� ����.
	public boolean boardModify(BoardBean modifyboard) throws Exception{
		
		String sql="update qna set QNA_SUBJECT=?,QNA_CONTENT=? where ID=?";
		
		try{
			con = connector.getConnection();
			pstmt = con.prepareStatement(sql);
			pstmt.setString(1, modifyboard.getQNA_SUBJECT());
			pstmt.setString(2, modifyboard.getQNA_CONTENT());
			pstmt.setInt(3, modifyboard.getID());
			pstmt.executeUpdate();
			return true;
		}catch(Exception ex){
			System.out.println("boardModify ���� : " + ex);
		}finally{
			connector.releaseConnection(con, pstmt, rs);
			}
		return false;
	}


	
	
	
	//�� ����.
	public boolean boardDelete(int ID){
		
		String board_delete_sql="delete from qna where ID=?";
		
		int result=0;
		
		try{
			con = connector.getConnection();
			pstmt=con.prepareStatement(board_delete_sql);
			pstmt.setInt(1, ID);
			result=pstmt.executeUpdate();
			if(result==0)return false;
			
			return true;
		}catch(Exception ex){
			System.out.println("boardDelete ���� : "+ex);
		}	finally{
			connector.releaseConnection(con, pstmt, rs);
			
		}
		
		return false;
	}
	
	//��ȸ�� ������Ʈ.
	public void setReadCountUpdate(int ID) throws Exception{
		
		String sql="update qna set QNA_READCOUNT = "+
			"QNA_READCOUNT+1 where ID = "+ID;
		
		try{
			con = connector.getConnection();
			pstmt=con.prepareStatement(sql);
			pstmt.executeUpdate();
		}catch(SQLException ex){
			System.out.println("setReadCountUpdate ���� : "+ex);
		}
		finally{
			connector.releaseConnection(con, pstmt, rs);
		
	}
	}
	
	//�۾������� Ȯ��.
	public boolean isBoardWriter(int ID,String pass){
		
		String board_sql="select * from qna where ID=?";
		
		try{
			con = connector.getConnection();
			pstmt=con.prepareStatement(board_sql);
			pstmt.setInt(1, ID);
			rs=pstmt.executeQuery();
			rs.next();
			
			if(pass.equals(rs.getString("QNA_PASS"))){
				return true;
			}
		}catch(SQLException ex){
			System.out.println("isBoardWriter ���� : "+ex);
		}
	finally{
		connector.releaseConnection(con, pstmt, rs);
		
	}
		return false;
	}
	
	public int idGet (String id){
		con = connector.getConnection();
		int idnum = 0;
		try{
			
			String sql = "select `ID` from MEMBERS where Mem_Nick = ?";
			
			pstmt = con.prepareStatement( sql );
			
			pstmt.setString(1, id);
			
			rs = pstmt.executeQuery();
			
			if( rs.next()){
				idnum = rs.getInt("ID");
			}
		}catch(Exception e){
			 e.printStackTrace();
		}finally{
			connector.releaseConnection(con, pstmt, rs);
		}
		return idnum;
	}

}
	